package db.document;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import db.Database;
import db.history.SystemLogDB;
import po.AccountBookPo;
import po.DocumentPo;
import po.Po;
import po.SystemLogPo;
import po.条目清单单项po;
import po.现金费用单po;

public class CashExpensesDocumentDB extends DocumentDB{

	
	
	
	AccountBookPo ab;
	public CashExpensesDocumentDB(AccountBookPo ab) {
		super(ab);
		String createT_cash="CREATE TABLE IF NOT EXISTS `seac2`.`"+book.getName()+ "_Doc_cash_expense` ( `f_id` varchar(40) NOT NULL, `f_operator` varchar(20) NOT NULL, `f_bank_account` varchar(50) NOT NULL, `f_item` int(20) NOT NULL AUTO_INCREMENT, `f_total` bigint(20) NOT NULL, `f_isApproved` int(1) NOT NULL DEFAULT '0', `f_date` date NOT NULL,PRIMARY KEY (`f_item`),UNIQUE (`f_item`)) ENGINE=InnoDB DEFAULT CHARSET=utf8";
		String createT_cashItem="CREATE TABLE IF NOT EXISTS `seac2`.`"+book.getName()+ "_Doc_cash_expense_item` ( `f_id` int(20) NOT NULL AUTO_INCREMENT, `f_key` int(20) NOT NULL, `f_name` varchar(50) NOT NULL, `f_money` bigint(20) NOT NULL, `f_remark` varchar(50) NOT NULL,  PRIMARY KEY (`f_id`),UNIQUE KEY `f_id` (`f_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8";
		Connection c = Database.getConnection();
		Statement s;
		try {
			s = c.createStatement();
			s.execute(createT_cash);
			s.execute(createT_cashItem);
			s.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		

	}

	@Override
	public Po add(DocumentPo po) {
		List<条目清单单项po> list=((现金费用单po) po).get条目清单();
		String r_id;
		String operator=((现金费用单po) po).get操作员();
		String account=((现金费用单po) po).get银行账户();
		long total=((现金费用单po) po).get总额();
       
		String date=new SimpleDateFormat("yyyy-MM-dd").format(new Date());
		int total_num=0;
		Po m=new Po();
		Connection c=Database.getConnection();
		Statement s;
		try {
			int key=0;
			s = c.createStatement();
			ResultSet rs=s.executeQuery("select * from "+book.getName()+"_Doc_cash_expense");
			while(rs.next()){
				total_num++;
			}
			r_id=getDocumentId("XJFYD",total_num);
			s.executeUpdate("insert into "+book.getName()+"_Doc_cash_expense(f_id,f_operator,f_bank_account,f_total,f_date) values('"+r_id+"','"+operator+"','"+account+"','"+total+"','"+date+"')");
			ResultSet r=s.executeQuery("select f_item from "+book.getName()+"_Doc_cash_expense where f_id='"+r_id+"'");
			while(r.next()){
		    key=r.getInt("f_item");
			}
			for(int i=0;i<list.size();i++){
			s.executeUpdate("insert into "+book.getName()+"_Doc_cash_expense_item(f_key,f_name,f_money,f_remark) values('"+key+"','"+list.get(i).get条目名()+"','"+list.get(i).get金额()+"','"+list.get(i).get备注()+"')");
			}
			s.close();
			m.successed();
			SystemLogDB log=new SystemLogDB();
			SystemLogDB.iniSystemLogDB();
			log.add(new SystemLogPo(po.get操作员(),"add",r_id));
		} catch (SQLException e) {
			m.failed(e.getMessage());
			e.printStackTrace();
		}
		return m;
	}

	@Override
	public Po update(DocumentPo po) {
		List<条目清单单项po> list=((现金费用单po) po).get条目清单();
		String r_id=((现金费用单po) po).get单据编号();
		String operator=((现金费用单po) po).get操作员();
		String account=((现金费用单po) po).get银行账户();
		Po m=new Po();
		Connection c=Database.getConnection();
		Statement s;
		try {
			int key=0;
			s = c.createStatement();
		    if(operator!=null){
		    	s.addBatch("update "+book.getName()+"_Doc_cash_expense set f_operator='"+operator+"' where f_id='"+r_id+"'");
		    }
			if(account!=null){
				s.addBatch("update "+book.getName()+"_Doc_cash_expense set f_bank_account='"+account+"' where f_id='"+r_id+"'");
			}
			ResultSet r=s.executeQuery("select f_item from "+book.getName()+"_Doc_cash_expense where f_id='"+r_id+"'");
			while(r.next()){
		    key=r.getInt("f_item");
			}
			s.addBatch("delete from "+book.getName()+"_Doc_cash_expense_item where f_key='"+key+"'");
			for(int i=0;i<list.size();i++){
			s.addBatch("insert into "+book.getName()+"_Doc_cash_expense_item(f_key,f_name,f_money,f_remark) values('"+key+"','"+list.get(i).get条目名()+"','"+list.get(i).get金额()+"','"+list.get(i).get备注()+"')");
			}
			s.executeBatch();
			s.close();
			m.successed();
		} catch (SQLException e) {
			m.failed(e.getMessage());
			e.printStackTrace();
		}
		return m;
	}

	@Override
	public List<DocumentPo> getUnapproval() {
		List<DocumentPo> list=new ArrayList<DocumentPo>();
		Connection c=Database.getConnection();
		Statement s;Statement s2;
		try {
			s = c.createStatement();
			ResultSet rs=s.executeQuery("select * from "+book.getName()+"_Doc_cash_expense where f_isApproved='"+0+"'");
			while(rs.next()){
				现金费用单po po=new 现金费用单po();
				po.set单据编号(rs.getString(1));
				po.set操作员(rs.getString(2));
				po.set银行账户(rs.getString(3));
				po.set总额(rs.getLong(5));
				
				List<条目清单单项po> item=new ArrayList<条目清单单项po>();
				int key=rs.getInt(4);
				s2=c.createStatement();
				ResultSet rs2=s2.executeQuery("select * from "+book.getName()+"_Doc_cash_expense_item where f_key='"+key+"'");
				while(rs2.next()){
					String name=rs2.getString(3);
					int money=rs2.getInt(4);
					String remark=rs2.getString(5);
					item.add(new 条目清单单项po(name,money,remark));
				}
				s2.close();
				po.set条目清单(item);
				list.add(po);
		}
			s.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<DocumentPo> get(DocumentPo from, DocumentPo to) {
        List<DocumentPo> list=new ArrayList<DocumentPo>();
		String from_date;String to_date;
		from_date=from.getDate()==null? null:new SimpleDateFormat("yyyy-MM-dd").format(from.getDate());
		to_date=to.getDate()==null?null:new SimpleDateFormat("yyyy-MM-dd").format(to.getDate());
		String query_from=from_date==null? "":"and f_date>='"+from_date+"'";
		String query_to=to_date==null? "":"and f_date<='"+to_date+"'";
		Connection c=Database.getConnection();
		Statement s;Statement s2;ResultSet rs = null;
		try {
			s = c.createStatement();
			rs=s.executeQuery("select * from "+book.getName()+"_Doc_cash_expense where f_isApproved='"+1+"'"+query_from+query_to);
			while(rs.next()){
				现金费用单po po=new 现金费用单po();
				po.set单据编号(rs.getString(1));
				po.set操作员(rs.getString(2));
				po.set业务员(rs.getString(2));
                po.set银行账户(rs.getString(3));
                po.set总额(rs.getLong(5));
				List<条目清单单项po> item=new ArrayList<条目清单单项po>();
				int key=rs.getInt(4);
				s2=c.createStatement();
				ResultSet rs2=s2.executeQuery("select * from "+book.getName()+"_Doc_cash_expense_item where f_key='"+key+"'");
				while(rs2.next()){
					String name=rs2.getString(3);
					long money=rs2.getLong(4);
					String remark=rs2.getString(5);
					item.add(new 条目清单单项po(name,money,remark));
				}
				s2.close();
				po.set条目清单(item);;
				list.add(po);
		}
			s.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}


	@Override
	public Po approve(DocumentPo document) {
		Po m=new Po();
		String id=((现金费用单po)document).get单据编号();
		long total=((现金费用单po)document).get总额();
		String account=((现金费用单po)document).get银行账户();
		Connection c=Database.getConnection();
		Statement s;
		
		try {
			s=c.createStatement();
			ResultSet rs1=s.executeQuery("select f_isApproved from "+book.getName()+"_Doc_cash_expense where f_id='"+id+"'");
			while(rs1.next()){
				if(rs1.getInt(1)==1){
					m.failed("该单据已被审批！");
					return m;
				}
			}
			
			s.execute("update  "+book.getName()+"_Doc_cash_expense  set f_isApproved='1' where f_id='"+id+"'");
			
			//审批后
			s.execute("update "+book.getName()+"_account set money=(money-"+total+") where name='"+account+"'");
			m.successed();
		} catch (SQLException e) {
			m.failed(e.getMessage());
			e.printStackTrace();
		}
		
		
		return m;
	}

	
}
